package com.kitri.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.kitri.db.SetOfDataSource;
import com.kitri.domain.Course;

public class CourseDaoImpl implements CourseDao {
	@Override
	public Course select(String courseId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM COURSE WHERE COURSE_ID=?");

		final Course course = new Course();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		jdbcTemplate.query(sql.toString(), new Object[] { courseId },
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						// mapping
						course.setCourseId(rs.getString("course_id"));
						course.setCourseName(rs.getString("course_name"));
						course.setClassRoom(rs.getString("class_room"));
						course.setStartDate(rs.getDate("start_date"));
						course.setEndDate(rs.getDate("end_date"));
						course.setSubjectList(((SubjectDaoImpl) AppContext
								.getAppContext().getBean("subjectDao"))
								.selectOfCourse(rs.getString("course_id")));
					}
				});

		return course;
	}

	@Override
	public List<Course> selectAll() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM COURSE");
		sql.append(" ORDER BY COURSE_NAME");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		List<Course> list = new ArrayList<Course>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql
				.toString());

		for (Map<String, Object> row : rows) {
			Course course = new Course();

			course.setCourseId((String) row.get("course_id"));
			course.setCourseName((String) row.get("course_name"));
			course.setClassRoom((String) row.get("class_room"));
			course.setStartDate((Date) row.get("start_date"));
			course.setEndDate((Date) row.get("end_date"));
			course.setSubjectList(((SubjectDaoImpl) AppContext.getAppContext()
					.getBean("subjectDao")).selectOfCourse((String) row
					.get("course_id")));

			list.add(course);
		}

		return list;
	}

	@Override
	public boolean insert(Course course) {
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO MEMBER (");
		sql.append("COURSE_ID, ");
		sql.append("COURSE_NAME, ");
		sql.append("CLASS_ROOM, ");
		sql.append("START_DATE, ");
		sql.append("END_DATE");
		sql.append(") VALUES(");
		sql.append("concat(substr('C0000000', 1, 8-length(to_char(course_seq.nextval))), to_char(course_seq.nextval)), ");
		sql.append("?, ?, to_date(?, 'YYYY-MM-DD'),  to_date(?, 'YYYY-MM-DD'))");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate.update(
				sql.toString(),
				new Object[] { course.getCourseName(), course.getClassRoom(),
						sf.format(course.getStartDate()),
						sf.format(course.getEndDate()) });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean update(Course course) {
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE MEMBER SET ");
		sql.append("COURSE_NAME = ?, ");
		sql.append("CLASS_ROOM = ?, ");
		sql.append("START_DATE = to_date(?, 'YYYY-MM-DD'), ");
		sql.append("END_DATE = to_date(?, 'YYYY-MM-DD')");
		sql.append("WHERE COURSE_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate.update(
				sql.toString(),
				new Object[] { course.getCourseName(), course.getClassRoom(),
						sf.format(course.getStartDate()),
						sf.format(course.getEndDate()), course.getCourseId() });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean delete(String courseId) {
		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM COURSE ");
		sql.append("WHERE COURSE_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { courseId });

		if (result > 0)
			return true;
		return false;
	}

}
